# coding=utf-8
from django import forms

from xls_demo.db import models

class xlsForm(forms.ModelForm):
    class Meta:
        model = models.xls
        exclude = ['id']

class xlsUploadForm(forms.Form):
    excel = forms.FileField()
    _valid_extensions = ['.xls', '.xlsx']
    _fields = ['a', 'b', 'c', 'd']
    header = ['A', 'B', 'C', 'D']

    def clean(self):
        cleaned_data = super(xlsUploadForm, self).clean()
        excel = cleaned_data.get('excel')

        data = ''
        for chunk in excel.chunks():
            data += chunk

        xls = xlrd.open_workbook(file_contents=data)
        sheet = xls.sheet_by_index(0)

        self.sheet = sheet
        self.datemode = xls.datemode

        return cleaned_data

    def save(self, commit=True):
        def parse_xls(sheet, datemode):
            data_lst = []
            for i in xrange(1, sheet.nrows):
                try:
                    row = sheet.row_values(i)
                    if len(row) < 4:
                        continue
                    a = row[0]
                    b = row[1]
                    c = row[2]
                    d = row[3]
                    data_item = {
                        'row': i + 1,
                        'a':a,
                        'b':b,
                        'c':c,
                        'd':d,
                    }
                    data_lst.append(data_item)
                except:
                    continue
            return data_lst
        records = []
        data_lst = parse_xls(self.sheet, self.datemode)
        for item in data_lst:
            form = xlsForm(item)
            if form.is_valid():
                try:
                    obj = form.save()
                    records.append(obj)
                except:
                    pass
            else:
                continue

        return records

class userForm(forms.ModelForm):
    class Meta:
        model = models.user
        exclude = ['id']
        
class UserLoginForm(forms.ModelForm):
    class Meta:
        model = models.user
        fields  = ['username', 'password']


class userUploadForm(forms.Form):
    excel = forms.FileField()
    _valid_extensions = ['.xls', '.xlsx']
    _fields = ['username', 'password', 'realname', 'mobile', 'email', 'qq']
    header = ['昵称', '密码', '姓名', '电话', '邮箱', 'QQ']

    def clean(self):
        cleaned_data = super(userUploadForm, self).clean()
        excel = cleaned_data.get('excel')

        data = ''
        for chunk in excel.chunks():
            data += chunk

        xls = xlrd.open_workbook(file_contents=data)
        sheet = xls.sheet_by_index(0)

        self.sheet = sheet
        self.datemode = xls.datemode

        return cleaned_data

    def save(self, commit=True):
        def parse_xls(sheet, datemode):
            data_lst = []
            for i in xrange(1, sheet.nrows):
                try:
                    row = sheet.row_values(i)
                    if len(row) < 5:
                        continue
                    username = row[0]
                    password = row[1]
                    realname = row[2]
                    mobile = row[3]
                    email = row[4]
                    qq = row[5]
                    data_item = {
                        'row': i + 1,
                        'username':username,
                        'password':password,
                        'realname':realname,
                        'mobile':mobile,
                        'email':email,
                        'qq':qq,
                    }
                    data_lst.append(data_item)
                except:
                    continue
            return data_lst

        records = []
        data_lst = parse_xls(self.sheet, self.datemode)
        for item in data_lst:
            form = userForm(item)
            if form.is_valid():
                try:
                    obj = form.save()
                    records.append(obj)
                except:
                    pass
            else:
                continue

        return records
